package com.bruce.excel;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

/**
 * @author bruce
 * @date 18/12/13 10:07
 * @Description: 描述--excel的操作工具类
 */
public class ExcelOperateTool {
    public static void main(String[] args) throws Exception {
        String excelPath1 = "C:\\Users\\lenovo\\Desktop\\nnn\\333.xlsx"; //Excel2007
        String excelPath2 = "C:\\Users\\lenovo\\Desktop\\nnn\\all.xls"; //Excel2003以前（包括2003）
        //excel转换成json
        ArrayList<String> jsonStirngArrayList = xlsx_to_json(excelPath2);
        String targetFile = "F:\\IdeaTestData\\testData\\alljson4.txt";
        //结果写入文件
        writeTotxtFile(jsonStirngArrayList, targetFile);
    }

    /**
     * @param excelPath
     * @return ArrayList<String>
     * @throws IOException,InvalidFormatException
     * @description: excel文件生成对应的json字符串
     */
    public static ArrayList<String> xlsx_to_json(String excelPath) throws InvalidFormatException, IOException {
        ArrayList<String> jsonString = new ArrayList<String>();
        //Excel
        Workbook wb = WorkbookFactory.create(new FileInputStream(excelPath));
        //sheet
        Sheet sheet = wb.getSheetAt(0);
        //标题
        String[] headNames = null;
        //每行
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            if (i == 0) { //标题
                headNames = new String[row.getPhysicalNumberOfCells()];
                //每列
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    Cell cell = row.getCell(j);
                    headNames[j] = cell.getStringCellValue();
                }
            } else { //构造json
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("{");
                for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                    Cell cell = row.getCell(j);
                    String key = headNames[j];
                    String value = cell.getStringCellValue().replaceAll("[\\n\\r\"]", " "); //删除换行
                    if (j != row.getPhysicalNumberOfCells() - 1) {
                        stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"").append(",");
                    } else {
                        stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"");
                    }
                }
                stringBuilder.append("}");
                jsonString.add(stringBuilder.toString());
            }
        }
        wb.close();
        return jsonString;
    }

    /**
     * @param jsonString
     * @param targetFile
     * @return ArrayList<String>
     * @throws IOException,InvalidFormatException
     * @description: json写入文件
     */

    public static void writeTotxtFile(ArrayList<String> jsonString, String targetFile) throws IOException {
        PrintWriter printWriter = new PrintWriter(targetFile);
        for (String jsonStr : jsonString) {
            printWriter.println(jsonStr);
            printWriter.flush();
        }
        printWriter.close();
    }
}
